# _*_ coding:utf-8 _*_
# @File  : ruizy_database.py
# @Time  : 2022-01-17  10:11
# @Author: zizle

# 瑞智数据库
import datetime

import MySQLdb
from typing import List

from logger import logger
from .pool import ruizy_db_pool


class RuizyEDBConnection(object):
    def __init__(self, conn_name=''):
        self.conn_name = conn_name
        self.conn = ruizy_db_pool.connection()  # 从连接池取得一个连接
        self.cursor = self.conn.cursor(MySQLdb.cursors.DictCursor)  # 连接的游标

    def begin(self):
        self.conn.begin()

    def close(self):
        self.cursor.close()
        self.conn.close()

    def commit(self):
        self.conn.commit()

    def rollback(self):
        self.conn.rollback()

    def __enter__(self):
        self.begin()
        return self.cursor

    def __exit__(self, exc_type, exc_val, exc_tb):
        if exc_type:
            self.rollback()
            print(f'RuizyDB SQL Error:{ exc_type.__name__ }:{ exc_val }')
        else:
            self.commit()
        self.close()

    def create_table(self, table_param):
        insert_index_sql = 'INSERT INTO all_ruizy_sheet (source_plat,source_id,name_zh,frequency,unit,raw_source,' \
                           'startdate,three_plat,note,state,is_open,creator_id,variety) ' \
                           'VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);'
        insert_param = [table_param['source_plat'], table_param['source_id'], table_param['name_zh'],
                        table_param['frequency'], table_param['unit'], table_param['raw_source'],
                        table_param['startdate'], table_param['three_plat'], table_param['note'], table_param['state'],
                        table_param['is_open'], table_param['creator_id'], table_param['variety']]

        table_name = f'{table_param["source_plat"]}_{table_param["source_id"]}'
        new_table_sql = f'CREATE TABLE IF NOT EXISTS `{table_name}`(' \
                        f'`id` INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,' \
                        f'`datadate` DATE NOT NULL COMMENT "数据时间",' \
                        f'`datavalue` DOUBLE(16,4) NOT NULL COMMENT "数值") DEFAULT CHARSET="utf8mb4";'

        self.conn.begin()
        try:
            self.cursor.execute(insert_index_sql, insert_param)
            self.cursor.execute(new_table_sql)
        except MySQLdb.Error as e:
            self.conn.rollback()
            # print(e)
            logger.error('创建瑞智数据表失败{}:{}'.format(e.args[0], e.args[1]))
            return False
        else:
            return True
        finally:
            self.close()

    def query_table(self, kw=None, plat=None, frequency=None, page=1, page_size=30):
        ret_obj = {'records': [], 'total_count': 1, 'total_page': 1, 'current_page': 1}
        kw = kw.replace(' ', '%') if kw else '0'
        plat = plat if plat else '0'
        frequency = frequency if frequency else '0'
        start, offset = (page-1)*page_size, page_size
        query_sql = 'SELECT SQL_CALC_FOUND_ROWS id,source_plat,source_id,name_zh,frequency,unit,raw_source,' \
                    'startdate,enddate,create_time,refresh,state,three_plat,note,is_open,variety FROM all_ruizy_sheet ' \
                    'WHERE IF("0"=%s,TRUE,name_zh LIKE %s OR source_id=%s) ' \
                    'AND IF("0"=%s,TRUE,frequency=%s) ' \
                    'AND IF("0"=%s,TRUE,source_plat=%s) ' \
                    'ORDER BY create_time DESC,id DESC LIMIT %s,%s;'
        try:
            self.cursor.execute(query_sql, [kw, f'%{kw}%', kw, frequency, frequency, plat, plat,start, offset])
            ret_obj['records'] = self.cursor.fetchall()
            # 查询页码
            self.cursor.execute('SELECT FOUND_ROWS() AS total;')
            total_obj = self.cursor.fetchone()
            if total_obj:
                total_count = total_obj['total']  # 当前总页码
                ret_obj['total_count'] = total_count
                ret_obj['total_page'] = (total_count + page_size - 1) // page_size
                ret_obj['current_page'] = page
        except Exception as e:
            logger.error('查询瑞智数据表失败{}:{}'.format(e.args[0], e.args[1]))
        finally:
            self.close()
            return ret_obj

    def browser_table(self, group, kw=None):

        return {}

    def save_table(self, table_param: dict, table_values: List[dict], keep_conn=False):  # 保存数据表内容
        table_values.sort(key=lambda x: x['datadate'])
        end_date = table_values[-1]['datadate']
        refresh_date = datetime.datetime.today().strftime('%Y-%m-%d %H:%M:%S')
        table_name = f'{table_param["source_plat"]}_{table_param["source_id"]}'
        self.begin()
        try:
            # 插入数据
            sql = f'INSERT INTO {table_name} (datadate,datavalue) VALUES (%(datadate)s,%(datavalue)s);'
            self.cursor.executemany(sql, table_values)
            # 更新数据表信息
            self.cursor.execute('UPDATE all_ruizy_sheet SET enddate=%s,refresh=%s WHERE id=%s LIMIT 1;',
                                [end_date, refresh_date, table_param['id']])
        except MySQLdb.Error as e:
            self.rollback()
            logger.error(f'保存数据表{table_name}失败:{e.args[0]}:{e.args[1]}')
            return False
        else:
            self.commit()
            return True
        finally:
            if not keep_conn:
                self.close()

    async def query_formula(self, need_tables):  # 查询需要的数据
        ret_data = {}
        try:
            for table in need_tables:
                self.cursor.execute('SELECT datadate,datavalue FROM %s;' % table)
                ret = self.cursor.fetchall()
                ret_data[table] = list(ret).copy()
        except MySQLdb.Error as e:
            logger.error(f'瑞智数据库连表查询数据失败:{e.args[0]}:{e.args[1]}')
            return None
        else:
            return ret_data

    def execute(self, sql, param=None, many=False, keep_conn=False):
        success = True
        self.begin()
        if param is None:
            param = []
        try:
            if many:
                count = self.cursor.executemany(sql, param)
            else:
                count = self.cursor.execute(sql, param)
        except MySQLdb.Error as e:
            self.rollback()
            success = False
            logger.error('操作数据库失败-{}:{}'.format(self.conn_name, e.args[0], e.args[1]))
        else:
            self.commit()
        finally:
            if not keep_conn:
                self.close()
            return success

    def query(self, sql, param=None, fetchone=False, keep_conn=False) -> list:
        records = []
        try:
            if param is None:  # 无参数
                self.cursor.execute(sql)
            else:
                self.cursor.execute(sql, param)
            if fetchone:
                record_obj = self.cursor.fetchone()
                if record_obj:
                    records.append(record_obj)
                else:
                    records.append({})
            else:
                records = list(self.cursor.fetchall())
        except MySQLdb.Error as e:
            logger.error('查询数据失败-{}:{}'.format(self.conn_name, e.args[0], e.args[1]))
        finally:
            if not keep_conn:
                self.close()
            return records

    def query_paginator(self, sql, param, page=1, page_size=10, keep_conn=False) -> dict:
        sql = sql.replace('SELECT ', 'SELECT SQL_CALC_FOUND_ROWS ', 1)  # 替换第一个
        ret_obj = {'data': [], 'total_count': 1, 'total_page': 1, 'page': 1}
        try:
            self.cursor.execute(sql) if param is None else self.cursor.execute(sql, param)
            ret_obj['data'] = self.cursor.fetchall()
            # 查询一下总数
            total_obj = self.query('SELECT FOUND_ROWS() AS total;', fetchone=False, keep_conn=keep_conn)[0]
            total_count = total_obj['total'] if total_obj else 0  # 当前总数
            ret_obj['total_count'] = total_count
            ret_obj['page'] = page
            ret_obj['page_size'] = page_size
            ret_obj['total_page'] = (total_count + page_size - 1) // page_size
        except MySQLdb.Error as e:
            logger.error(f'分页查询数据库失败:{e}\nSQL: {sql}')
        finally:
            if not keep_conn:
                self.close()
            return ret_obj

    def execute_tasks(self, sql_list, param_list, keep_conn=False, many=False):  # 事务方式执行
        self.begin()
        is_success = True
        try:
            if many:
                for sql_tuple in zip(sql_list, param_list):
                    if sql_tuple[1] is None:
                        self.cursor.executemany(sql_tuple[0])
                    else:
                        self.cursor.executemany(sql_tuple[0], sql_tuple[1])
            else:
                for sql_tuple in zip(sql_list, param_list):
                    if sql_tuple[1] is None:
                        self.cursor.execute(sql_tuple[0])
                    else:
                        self.cursor.execute(sql_tuple[0], sql_tuple[1])
        except MySQLdb.Error as e:
            self.rollback()
            logger.error('事务型操作数据库失败{}:{}'.format(e.args[0], e.args[1]))
            is_success = False
        else:
            self.commit()
        finally:
            if not keep_conn:
                self.close()
            return is_success
